insert overwrite table jms_dm.dm_employee_operate_effective_dt
--班组维度统计
select
    t2.update_time  as date_time       -- 日期
     ,net.agent_code        --代理区code
     ,net.agent_name        --代理区name
     ,t2.center_code       --中心code
     ,t2.center_name       --中心名称
     ,t2.institution_code  --标准组织code
     ,t2.institution_name  --标准组织name
     ,t2.institutiona_code --组织code
     ,t2.institutiona_name --组织name
     ,t2.schedule_code     --班次code
     ,t2.schedule_name     --班次name
     ,(nvl(t2.operate_num,0)+nvl(rely.reply_operate_num,0))  as operate_num  --操作量
     ,t2.formal_number     --自有员工操作数
     ,t2.informal_number   --外包临时工人数
     ,cast((nvl(t2.operate_num,0)+nvl(rely.reply_operate_num,0))/(t2.formal_number+t2.informal_number ) as decimal(16,2)) as effectiveness  --效能
     ,up.operate_num      as up_operate_num     --操作量
     ,up.formal_number    as up_formal_number   --自有员工操作数
     ,up.informal_number  as up_informal_number --外包临时工人数
     ,up.effectiveness    as up_effectiveness   --上一日效能
     ,'institutiona' as org_type --班组维度
     ,t2.formal_hours
     ,t2.noformal_hours
     ,up.formal_hours  as up_formal_hours     --上一天在岗时长正式
     ,up.noformal_hours  as up_noformal_hours --上一天在岗时长临时
     ,(nvl(t2.per_oper_vo,0)+nvl(rely.reply_per_oper_vo,0))  as operate_bill_num  --操作量（票数）
     ,up.operate_bill_num  as up_operate_bill_num --上一天操作量（票数）
     ,cast((nvl(t2.per_oper_vo,0)+nvl(rely.reply_per_oper_vo,0))/(t2.formal_number+t2.informal_number ) as decimal(16,2)) as bill_effectiveness  --票数_效能
     ,up.bill_effectiveness as up_bill_effectiveness    --上一日票数_效能
     ,t2.update_time as dt
from
    (
        SELECT
            update_time
             ,center_code
             ,center_name
             ,institution_code
             ,institution_name
             ,institutiona_code
             ,institutiona_name
             ,schedule_code
             ,schedule_name
             ,sum(operate_num) as operate_num --操作量
             ,cast(sum(if(is_formal=1,hours_cpu,0))/10 as int)+
              (   case when (sum(if(is_formal=1,hours_cpu,0))/10-cast(sum(if(is_formal=1,hours_cpu,0))/10 as int))/0.5>1 then 1
                       when (sum(if(is_formal=1,hours_cpu,0))/10-cast(sum(if(is_formal=1,hours_cpu,0))/10 as int))/0.5=0 then 0
                       else 0.5 end
                  ) as formal_number  --自有操作人数
             ,cast(sum(if(is_formal=0,hours_cpu,0))/10 as int )+
              (    case when (sum(if(is_formal=0,hours_cpu,0))/10-cast(sum(if(is_formal=0,hours_cpu,0))/10 as int))/0.5>1 then 1
                        when (sum(if(is_formal=0,hours_cpu,0))/10-cast(sum(if(is_formal=0,hours_cpu,0))/10 as int))/0.5=0 then 0
                        else 0.5 end
                  ) as informal_number --外包临时工人数
             ,sum(if(is_formal=1,work_hours,0)) as formal_hours  --总工时、在岗时长 正式员工
             ,sum(if(is_formal=0,work_hours,0)) as noformal_hours --总工时、在岗时长 临时工
             ,sum(per_oper_vo) as per_oper_vo --操作票數
        FROM
            (
                select
                    update_time,center_code,center_name,institution_code,institution_name,institutiona_code,institutiona_name,schedule_code,schedule_name,employ_code,employ_name
                     --,if(employ_type =2,1,0) as is_formal
                     ,case when employ_type=2 then 1
                           when employ_type in (1,5) then 0
                           else -1 end as  is_formal  --是否正式员工
                     ,operate_num
                     ,case when operate_code!=1 then 0
                           when employ_type in (2,1,5) then work_hours
                           else 0
                    end as  work_hours  --正式大于10h算1人
                     ,case when operate_code!=1 then 0
                           when employ_type=2  and work_hours>=10 then 10
                           when employ_type=4  then 0 --职工不计算
                           else work_hours
                    end as  hours_cpu  --正式大于10h算1人
                     ,per_oper_vo --操作票數
                from jms_dm.dm_employee_operate_num_detail a
                where dt =date_add('{{ execution_date | cst_ds }}',-1)

            )t
        group by update_time,center_code,institution_code,institutiona_code,schedule_code
               ,center_name,institution_name,institutiona_name,schedule_name
    )t2 left join jms_dim.dim_network_whole_massage net on net.code=t2.center_code
    left join
    (  --获取被挂靠班组的操作量赋值给挂靠班组
        select
            t.center_code,t.institution_code,t.schedule_code,t2.reply_operate_num,t2.reply_per_oper_vo
        from
            (
                select center_code,institution_code,rely_institution_code,schedule_code
                from jms_dwd.dwd_ep_create_institution_base
                where date(create_day)=date_add('{{ execution_date | cst_ds }}',-1)
                group by center_code,institution_code,schedule_code,rely_institution_code
            )t
            left join
            (
                select center_code,institution_code,sum(operate_num) reply_operate_num,schedule_code,sum(per_oper_vo) as reply_per_oper_vo
                from jms_dm.dm_employee_operate_num_detail
                where dt=date_add('{{ execution_date | cst_ds }}',-1)
                group by center_code,institution_code,schedule_code
            )t2  on t.rely_institution_code=t2.institution_code  and  t.center_code=t2.center_code and  t.schedule_code=t2.schedule_code
    )rely on  t2.center_code=rely.center_code and t2.institution_code=rely.institution_code and t2.schedule_code=rely.schedule_code
    --关联上一天获取上一天的指标值
    left join jms_dm.dm_employee_operate_effective_dt up on up.dt=date_add(date_add('{{ execution_date | cst_ds }}',-1),-1) and  up.org_type='institutiona'
    and up.dt=date_add(t2.update_time,-1) and up.center_code=t2.center_code and up.institution_code=t2.institution_code and up.institutiona_code=t2.institutiona_code  and up.schedule_code=t2.schedule_code

union ALL
--中心维度统计
select
    t2.update_time as date_time       -- 日期
     ,net.agent_code        --代理区code
     ,net.agent_name        --代理区name
     ,t2.center_code       --中心code
     ,net.name as center_name       --中心名称
     ,null as institution_code  --标准组织code
     ,null as institution_name  --标准组织name
     ,null as institutiona_code --组织code
     ,null as institutiona_name --组织name
     ,null as schedule_code     --班次code
     ,null as schedule_name     --班次name
     ,nvl(t2.operate_num,0)+nvl(out_cnt.work_out_cnt,0) as  operate_num     --操作量
     ,t2.formal_number     --自有员工操作数
     ,t2.informal_number   --外包临时工人数
     ,cast((nvl(t2.operate_num,0)+nvl(out_cnt.work_out_cnt,0))/(t2.formal_number+t2.informal_number ) as decimal(16,2)) as effectiveness  --效能
     ,up.operate_num      as up_operate_num     --操作量（件数）
     ,up.formal_number    as up_formal_number   --自有员工操作数
     ,up.informal_number  as up_informal_number --外包临时工人数
     ,up.effectiveness    as up_effectiveness   --上一日效能
     ,'center' as org_type --统计维度
     ,t2.formal_hours
     ,t2.noformal_hours
     ,up.formal_hours    as up_formal_hours   --上一天在岗时长正式
     ,up.noformal_hours  as up_noformal_hours --上一天在岗时长临时
     ,nvl(t2.per_oper_vo,0)+nvl(out_cnt.work_out_bill_cnt,0) as operate_bill_num --操作票数
     ,up.operate_bill_num as up_operate_bill_num --上一天操作票数
     ,cast((nvl(t2.per_oper_vo,0)+nvl(out_cnt.work_out_bill_cnt,0))/(t2.formal_number+t2.informal_number) as decimal(16,2)) as bill_effectiveness  --票数_效能
     ,up.bill_effectiveness as up_bill_effectiveness    --上一日票数_效能
     ,t2.update_time as dt
from
    (
        SELECT
            update_time
             ,center_code
             ,sum(operate_num) as operate_num--操作量
             ,cast(sum(if(is_formal=1,hours_cpu,0))/10 as int)+
              (
                  case when (sum(if(is_formal=1,hours_cpu,0))/10-cast(sum(if(is_formal=1,hours_cpu,0))/10 as int))/0.5>1 then 1
                       when (sum(if(is_formal=1,hours_cpu,0))/10-cast(sum(if(is_formal=1,hours_cpu,0))/10 as int))/0.5=0 then 0
                       else 0.5 end
                  )
                               as formal_number --自有操作人数
             ,cast(sum(if(is_formal=0,hours_cpu,0))/10 as int )+
              (
                  case when (sum(if(is_formal=0,hours_cpu,0))/10-cast(sum(if(is_formal=0,hours_cpu,0))/10 as int))/0.5>1 then 1
                       when (sum(if(is_formal=0,hours_cpu,0))/10-cast(sum(if(is_formal=0,hours_cpu,0))/10 as int))/0.5=0 then 0
                       else 0.5 end
                  ) as informal_number --外包临时工人数
             ,sum(if(is_formal=1,work_hours,0)) as formal_hours  --总工时、在岗时长 正式员工
             ,sum(if(is_formal=0,work_hours,0)) as noformal_hours --总工时、在岗时长 临时工
             ,sum(per_oper_vo) as per_oper_vo
        FROM
            (--获取中心工时
                select
                    update_time
                     ,center_code
                     ,employ_code
                     ,is_formal  --是否正式员工
                     ,operate_num
                     ,work_hours
                     ,case when is_formal=1 and  work_hours>=10 then 10
                           when is_formal=0 then work_hours
                           else work_hours
                    end as hours_cpu
                     ,per_oper_vo
                from
                    (
                        select
                            update_time
                             ,center_code
                             ,employ_code
                             --员工属性1临时工/2正式工/3小时工(已废弃)/4职能/5环节外包
                             ,case when employ_type=2 then 1
                                   when employ_type in (1,5) then 0
                                   else -1 end as  is_formal  --是否正式员工 --是否正式 --是否正式
                             ,sum(if(linked_method='转运中心装车',operate_num,0)) as operate_num
                             ,sum(if(linked_method='转运中心装车',per_oper_vo,0))  as per_oper_vo
                             ,sum(case when operate_code!=1 then 0
                                       when employ_type in (2,1,5) then work_hours
                                       else 0
                            end) as  work_hours
                        from jms_dm.dm_employee_operate_num_detail a
                        where dt=date_add('{{ execution_date | cst_ds }}',-1)
                        group by update_time,center_code,employ_code,employ_type
                    )gr
            )t group by update_time,center_code
    )t2
        left join jms_dim.dim_network_whole_massage net on net.code=t2.center_code
        left join ( --组外操作量只计算转运中心装车的
        select date_time,center_code
             ,sum(work_out_cnt) as work_out_cnt
             ,sum(work_out_bill_cnt) as work_out_bill_cnt
        from jms_dm.dm_employee_work_out_cnt_dt
        where dt=date_add('{{ execution_date | cst_ds }}',-1)  and scan_type='转运中心装车'
        group by date_time,center_code
    )out_cnt on out_cnt.date_time=t2.update_time and out_cnt.center_code=t2.center_code
        left join jms_dm.dm_employee_operate_effective_dt up on up.dt=date_add('{{ execution_date | cst_ds }}',-2) and up.org_type='center'
        and up.dt=date_add(t2.update_time,-1) and up.center_code=t2.center_code
    distribute by 1;